package net.araim.bbingo.db;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import net.araim.bbingo.Shame;
import net.araim.bbingo.activity.CorporateBingo;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class BingoDB extends SQLiteOpenHelper {

	private static final String[] factoryTexts = new String[] { "Synergy", "Strategic fit", "Burn velocity", "Out of the box",
			"Bottom line", "Revisit", "Return on investment", "24/7", "Focus", "Gap Analysis", "Value-added", "Proactive", "Win-win",
			"Think outside the box", "Fast track", "Result-driven", "Empower", "Knowledge base", "Vision", "Touch base", "Mindset",
			"Audit", "Ballpark", "Game plan", "Leverage", "Cascade", "Green light", "Very much out of the box" };

	private static final String DATABASE_NAME = "net.araim.bbingo";
	private static final int DATABASE_VERSION = 1;
	private static final SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy HH:mm"); // ("EEEE, MMM dd yyyy; HH mm");

	public BingoDB(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	private void resetTexts(SQLiteDatabase db) {
		db.execSQL("DROP TABLE texts");
		db.execSQL("DROP TABLE stats");
		db.execSQL("CREATE TABLE texts (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
		db.execSQL("CREATE TABLE stats (_id INTEGER, cnt INTEGER)");
		ContentValues cv = new ContentValues();
		for (String s : factoryTexts) {
			cv.clear();
			cv.put("name", s);
			long id = db.insert("texts", null, cv);

			cv.clear();
			cv.put("_id", id);
			cv.put("cnt", 0);
			db.insert("stats", null, cv);
		}
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE shame (_id INTEGER PRIMARY KEY AUTOINCREMENT, meetingdate TEXT, meetingname TEXT, secs INTEGER)");
		db.execSQL("CREATE TABLE texts (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
		db.execSQL("CREATE TABLE stats (_id INTEGER, cnt INTEGER)");
		for (String s : factoryTexts) {
			db.execSQL("INSERT INTO texts (name) VALUES (?)", new String[] { s });
		}
	}

	public void addText(String txt){
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("INSERT INTO texts (name) VALUES (?)", new String[] { txt });
	}
	
	public void deleteTexts(Integer[] ids){
		SQLiteDatabase db = this.getWritableDatabase();
		for (Integer i : ids) {
			db.execSQL("DELETE FROM texts WHERE _id = ?", new Integer[] { i });
		}
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVer, int newVer) {
		if (newVer < 2) {
			db.execSQL("DROP TABLE texts");
			db.execSQL("DROP TABLE shame");
			db.execSQL("DROP TABLE stats");
			onCreate(db);
		}
	}

	public void resetFactorySettings() {
		SQLiteDatabase db = this.getWritableDatabase();
		resetTexts(db);
	}

	public String[] getAllTexts() {
		ArrayList<String> texts = new ArrayList<String>();
		SQLiteDatabase db = this.getWritableDatabase();
		db.close();
		db = null;
		Cursor c = null;
		try {
			db = this.getReadableDatabase();
			c = db.query("texts", new String[] { "name" }, null, null, null, null, null);
			if (c.moveToFirst()) {
				while (!c.isAfterLast()) {
					texts.add(c.getString(0));
					c.moveToNext();
				}
			}
		} catch (Exception e) {
			Log.e(CorporateBingo.LOGTAG, "Exception fetching texts: " + e.toString());
		} finally {
			if (c != null) {
				c.close();
			}
			db.close();
		}
		return texts.toArray(new String[0]);
	}

	public Cursor getAllTextsCursor() {
		Cursor c = null;
		SQLiteDatabase db = null;
		try {
			db = this.getWritableDatabase();
			c = db.query("texts", new String[] { "_id", "name" }, null, null, null, null, "name ASC");
		} catch (Exception e) {
			Log.e(CorporateBingo.LOGTAG, "Exception fetching texts: " + e.toString());
		}
		return c;
	}

	public Shame[] getAllshame() {
		ArrayList<Shame> shames = new ArrayList<Shame>();
		SQLiteDatabase db = this.getWritableDatabase();
		db.close();
		db = null;
		Cursor c = null;
		try {
			db = this.getReadableDatabase();
			c = db.query("shame", new String[] { "meetingname", "meetingdate", "secs" }, null, null, null, null, "secs ASC");
			if (c.moveToFirst()) {
				while (!c.isAfterLast()) {
					Shame s = new Shame();
					s.setMeetingName(c.getString(0));
					s.setMeetingDate(c.getString(1));
					s.setSecs(c.getInt(2));
					shames.add(s);
					c.moveToNext();
				}
			}
		} catch (Exception e) {
			Log.e(CorporateBingo.LOGTAG, "Exception fetching texts: " + e.toString());
		} finally {
			if (c != null) {
				c.close();
			}
			db.close();
		}
		return shames.toArray(new Shame[0]);
	}

	public void saveResult(String meetingName, Date start, Date end) {
		ContentValues cv = new ContentValues();

		cv.put("meetingdate", sdf.format(end));
		cv.put("meetingname", meetingName);
		cv.put("secs", (end.getTime() - start.getTime()) / 1000);

		SQLiteDatabase db = this.getWritableDatabase();
		db.insert("shame", null, cv);
		ensureTenStays(db);
	}

	private void ensureTenStays(SQLiteDatabase db) {
		db.execSQL("DELETE FROM shame WHERE _id IN (SELECT _id FROM shame  ORDER BY secs ASC limit -1 offset 10)");
	}

	public void resetShames() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("DELETE FROM shame");

	}

	public long insertText(String text){
		//TODO: implement :P
		return 0L;
	}
	
}
